package cn.cmcc.online.smsapi.sdk.db.entity.pubinfo;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

import android.content.ContentValues;
import android.database.sqlite.SQLiteDatabase;
import cn.cmcc.online.smsapi.sdk.constant.Constant;
import cn.cmcc.online.smsapi.sdk.db.DBManager;
import cn.cmcc.online.smsapi.sdk.db.XyCursor;
import cn.cmcc.online.smsapi.sdk.db.base.BaseManager;
import cn.cmcc.online.smsapi.sdk.db.entity.SmsParseRecorder;
import cn.cmcc.online.smsapi.sdk.db.entity.TaskInfo;
import cn.cmcc.online.smsapi.sdk.dex.DexUtil;
import cn.cmcc.online.smsapi.sdk.log.LogManager;
import cn.cmcc.online.smsapi.sdk.net.util.Signaturer;
import cn.cmcc.online.smsapi.sdk.util.DateUtils;
import cn.cmcc.online.smsapi.sdk.util.StringUtils;

/* RM-1351 huangzhiqiang 20170407 start */
public class CountSignInfoManager {
    public static final String ID = "id";
    public static final String NUM = "num";// 公众号
    public static final String DATA_GROUP = "data_group";// 数据组，用于统计识别次数，格式SHA256(号码_签名1;签名2_识别时间)
    public static final String SIGN_GROUP_KEY = "sign_group_key";// 签名组Key，用于获取对应的签名信息，格式SHA256(签名1;签名2)
    public static final String PARSE_DATE = "parse_date";// 签名识别时间
    public static final String PARSE_COUNT = "parse_count"; // 签名识别次数
    public static final String STATUS = "status"; // 请求状态(0:未请求 1:已请求)，构造公众号数据请求报文时只读取“未请求”的数据，避免重复上报签名密文数据
    public static final String SIGN_GROUP_ID = "sign_group_id";
    public static final String SIGN = "sign"; // 签名明文
    public static final String CODE = "code"; // 签名明文的SHA256密文

    private static final String COUNT_SIGN_INFO_TABLE_NAME = "tb_count_sign_info";
    private static final String SIGN_GROUP_TABLE_NAME = "tb_sign_group";
    public static final String DROP_COUNT_SIGN_INFO_TABLE = " DROP TABLE IF EXISTS " + COUNT_SIGN_INFO_TABLE_NAME;
    public static final String DROP_SIGN_GROUP_TABLE = " DROP TABLE IF EXISTS " + SIGN_GROUP_TABLE_NAME;

    public static final String SPLIT = ";";
    public static final String SPLIT2 = "_";
    public static final String SPLIT3 = "|";
    public static final String YYYYMMDD = "yyyyMMdd";
    public static final String EMPTY_SIGN = "EmPtY"; // 无签名时使用的占位签名
    public static final String EMPTY_SIGN_CODE = "4144759d6b03ac760966a34164f31e9592c9e2fb5ac64c58ba89a48b1183d175"; // SHA256(无签名时使用的占位签名)
    public static final int DEFAULT_MAX_UPLOAD_SIGN_DATA = 10; // 默认最多可上传多少个签名的签名统计数据
    public static final int STATUS_NEED_REQUEST = 0;
    public static final int STATUS_REQUEST_COMPLETE = 1;

    public static String getCreateCountSignInfoTableSql() {
        return "CREATE TABLE IF NOT EXISTS " + COUNT_SIGN_INFO_TABLE_NAME + " (" + ID + " INTEGER PRIMARY KEY," + NUM
                + " TEXT NOT NULL," + DATA_GROUP + " TEXT NOT NULL," + SIGN_GROUP_KEY + " TEXT NOT NULL," + PARSE_DATE
                + " TEXT NOT NULL," + PARSE_COUNT + " INTEGER DEFAULT 0," + STATUS + " INTEGER DEFAULT 0)";
    }

    public static String getCreateSignGroupTableSql() {
        return "CREATE TABLE " + SIGN_GROUP_TABLE_NAME + " (" + ID + "  INTEGER," + SIGN_GROUP_KEY + "  TEXT," + SIGN
                + "  TEXT NOT NULL," + CODE + "  TEXT NOT NULL,PRIMARY KEY (" + ID + " ASC),UNIQUE (" + SIGN_GROUP_KEY
                + " ASC, " + SIGN + " ASC))";
    }

    
    /**
     * 新增或更新签名统计数据
     * 
     * @param num
     * @param signArr
     * @return
     */
    public static long saveOrUpdateCountInfo(String num, String[] signArr) {
        if (StringUtils.isNull(num) || signArr == null || signArr.length == 0) {
            return -1;
        }
        try {
            String signGroup = createSignGroup(signArr);
            if (StringUtils.isNull(signGroup)) {
                return -1;
            }
            String signGroupKey = createSignGroupKey(signGroup);
            String parseDate = DateUtils.getTimeString("yyyyMMdd", System.currentTimeMillis());
            String dataGroup = createDataGroup(num, signGroup, parseDate);
            CountSignInfo countSignInfo = queryDataByDataGroupKey(dataGroup);
            if (countSignInfo == null) {
                return insert(num, dataGroup, signGroupKey, parseDate, 1, STATUS_NEED_REQUEST, signArr);
            }

            // 已经上传过该签名统计数据则重置识别次数为1
            if (countSignInfo.status == STATUS_REQUEST_COMPLETE) {
                countSignInfo.parseCount = 1;
                countSignInfo.status = STATUS_NEED_REQUEST;
            } else {
                countSignInfo.parseCount++;
            }
            return DBManager.update(COUNT_SIGN_INFO_TABLE_NAME, BaseManager.getContentValues(null, PARSE_COUNT,
                    String.valueOf(countSignInfo.parseCount), STATUS, String.valueOf(countSignInfo.status)), DATA_GROUP
                    + " = ? ", new String[] { dataGroup });
        } catch (Throwable e) {
            LogManager.w(Constant.TAG, "CountSignInfoManager saveOrUpdateCountInfo error", e);
        }
        return -1;
    }

    /**
     * 生成签名组Key，格式：SHA256(签名1;签名2)
     * 
     * @param num
     * @param signGroup
     * @param parseTime
     * @return
     */
    private static String createSignGroupKey(String signGroup) {
        if (StringUtils.isNull(signGroup)) {
            return "";
        }
        return Signaturer.sha256Encode(signGroup);
    }

    /**
     * 生成签名组，多个签名以;隔开，格式：签名1;签名2
     * 
     * @return
     */
    public static String createSignGroup(String[] signArr) {
        if (signArr == null || signArr.length == 0) {
            return "";
        }
        StringBuilder sbrSignGroup = new StringBuilder();
        String sign = null;
        for (int i = 0; i < signArr.length; i++) {
            sign = signArr[i];
            if (StringUtils.isNull(sign)) {
                continue;
            }
            sbrSignGroup.append(sign).append(SPLIT);
        }
        return (sbrSignGroup.length() == 0) ? "" : sbrSignGroup.deleteCharAt(sbrSignGroup.length() - 1).toString();
    }

    /**
     * 新增签名统计数据
     * 
     * @param num
     * @param signGroupKey
     * @param dataGroup
     * @param parseDate
     * @param parseCount
     * @param status
     * @param signArr
     * @return
     */
    private static long insert(String num, String dataGroup, String signGroupKey, String parseDate, int parseCount,
            int status, String[] signArr) {
        SQLiteDatabase db = null;
        try {
            db = DBManager.getSQLiteDatabase();
            db.beginTransaction();

            long result = db.insert(COUNT_SIGN_INFO_TABLE_NAME, null, BaseManager.getContentValues(null, NUM, num,
                    DATA_GROUP, dataGroup, SIGN_GROUP_KEY, signGroupKey, PARSE_DATE, parseDate, PARSE_COUNT,
                    String.valueOf(parseCount), STATUS, String.valueOf(status)));
            if (result < 1) {
                return -1;
            }

            String sign = null;
            String code = null;
            for (int i = 0; i < signArr.length; i++) {
                sign = signArr[i];
                code = Signaturer.sha256Encode(sign);
                result = db.insertWithOnConflict(SIGN_GROUP_TABLE_NAME, null,
                        BaseManager.getContentValues(null, SIGN_GROUP_KEY, signGroupKey, SIGN, sign, CODE, code), SQLiteDatabase.CONFLICT_REPLACE);
                if (result < 1) {
                    break;
                }
            }
            if (result > 0) {
                db.setTransactionSuccessful();
            }
            return result;
        } catch (Throwable e) {
            LogManager.w(Constant.TAG, "CountSignInfoManager insert error", e);
            return -1;
        } finally {
            if (db != null) {
                try {
                    if (db.inTransaction()) {
                        db.endTransaction();
                    }
                } catch (Throwable e) {
                    LogManager.w(Constant.TAG, "CountSignInfoManager insert error", e);
                } finally {
                    DBManager.close(db);
                }
            }
        }
    }

    /**
     * 更新指定号码的所有签名统计请求状态
     * 
     * @param num
     * @param status 请求状态(0:未请求 1:已请求)
     * @return
     */
    public static int updateStatusByNum(String num, int status) {
        try {
            if (StringUtils.isNull(num)) {
                return -1;
            }
            ContentValues contentValues = BaseManager.getContentValues(null,
                    new String[] { STATUS, String.valueOf(status) });
            return DBManager.update(COUNT_SIGN_INFO_TABLE_NAME, contentValues, NUM + " = ?", new String[] { num });
        } catch (Throwable e) {
            LogManager.w(Constant.TAG, "CountSignInfoManager updateStatusByNum error", e);
        }
        return -1;
    }

    /**
     * 根据数据组Key获取签名统计数据
     * 
     * @param signGroupKey
     * @return
     */
    private static CountSignInfo queryDataByDataGroupKey(String dataGroup) {
        List<CountSignInfo> countSignInfoList = query(DATA_GROUP + "=?", new String[] { dataGroup }, SIGN_GROUP_ID);
        return (countSignInfoList == null || countSignInfoList.size() == 0) ? null : countSignInfoList.get(0);
    }

    /**
     * 获取指定号码的所有签名统计数据
     * 
     * @param num
     * @return
     */
    public static List<CountSignInfo> queryDataByNum(String num, boolean needRequest) {
        return query(NUM + "=?" + (needRequest ? (" AND " + STATUS + "=" + STATUS_NEED_REQUEST) : ""),
                new String[] { num }, SIGN_GROUP_KEY + "," + DATA_GROUP+ "," + SIGN_GROUP_ID);
    }

    /**
     * 根据查询条件获取签名统计数据(必须以data_group及sign_group_id排序，否则返回结果可能有误)
     * 
     * @param whereClause
     * @param whereArgs
     * @param orderBy
     * @return
     */
    private static List<CountSignInfo> query(String whereClause, String[] whereArgs, String orderBy) {
        XyCursor cursor = null;
        try {
            StringBuilder sbrSql = new StringBuilder();
            sbrSql.append("SELECT * FROM (SELECT cpi.id,cpi.num,cpi.data_group,cpi.sign_group_key,cpi.parse_date,cpi.parse_count,cpi.status,sg.id sign_group_id,sg.sign,sg.code FROM tb_count_sign_info cpi LEFT JOIN tb_sign_group sg ON sg.sign_group_key=cpi.sign_group_key)");
            if (!StringUtils.isNull(whereClause)) {
                sbrSql.append(" WHERE ").append(whereClause);
            }
            if (!StringUtils.isNull(orderBy)) {
                sbrSql.append(" ORDER BY ").append(orderBy);
            } else {
                sbrSql.append(" ORDER BY num,data_group,sign_group_id");
            }
            cursor = DBManager.rawQuery(sbrSql.toString(), whereArgs);
            if (cursor == null || cursor.getCount() < 1) {
                return null;
            }
            List<CountSignInfo> countSignInfoList = new ArrayList<CountSignInfo>();
            StringBuilder sbrSignCode = new StringBuilder();
            CountSignInfo countSignInfo = null;
            String dataGroup = null;
            boolean isNewDataGroup = false;
            int idIndex = cursor.getColumnIndex(ID);
            int numIndex = cursor.getColumnIndex(NUM);
            int dataGroupIndex = cursor.getColumnIndex(DATA_GROUP);
            int signGroupKeyIndex = cursor.getColumnIndex(SIGN_GROUP_KEY);
            int parseDateIndex = cursor.getColumnIndex(PARSE_DATE);
            int parseCountIndex = cursor.getColumnIndex(PARSE_COUNT);
            int statusIndex = cursor.getColumnIndex(STATUS);
            int codeIndex = cursor.getColumnIndex(CODE);
            while (cursor.moveToNext()) {
                dataGroup = cursor.getString(dataGroupIndex);
                isNewDataGroup = countSignInfo != null && !countSignInfo.dataGroup.equals(dataGroup);
                // 出现新的签名统计数据，为上一个签名统计设置签名组数据，并重置签名组List
                if (isNewDataGroup) {
                    countSignInfo.signCode = sbrSignCode.deleteCharAt(sbrSignCode.length() - 1).toString();
                    sbrSignCode.setLength(0);
                }
                if (countSignInfo == null || isNewDataGroup) {
                    countSignInfo = new CountSignInfo();
                    countSignInfo.id = cursor.getLong(idIndex);
                    countSignInfo.num = cursor.getString(numIndex);
                    countSignInfo.dataGroup = dataGroup;
                    countSignInfo.signGroupKey = cursor.getString(signGroupKeyIndex);
                    countSignInfo.parseDate = cursor.getString(parseDateIndex);
                    countSignInfo.parseCount = cursor.getInt(parseCountIndex);
                    countSignInfo.status = cursor.getInt(statusIndex);
                    countSignInfoList.add(countSignInfo);
                }
                sbrSignCode.append(cursor.getString(codeIndex)).append(SPLIT);
            }

            // 设置最后一个签名统计数据的密文签名数据
            if (sbrSignCode.length() > 0) {
                countSignInfo.signCode = sbrSignCode.deleteCharAt(sbrSignCode.length() - 1).toString();
            }
            return countSignInfoList;
        } catch (Throwable e) {
            LogManager.w(Constant.TAG, "CountSignInfoManager query error", e);
        } finally {
            XyCursor.closeCursor(cursor, true);
        }
        return null;
    }

    /**
     * 删除历史数据
     */
    public static void deleteHistoryData() {
        try {
            if (!DexUtil.mRunNewUploadPubInfoSign) {
                return;
            }
            // 清除30天前的签名统计历史数据
            String date = DateUtils.getTimeString(YYYYMMDD, (System.currentTimeMillis() - 2592000000L));
            DBManager.delete(COUNT_SIGN_INFO_TABLE_NAME, " parse_date < ? ", new String[] { date });
            // 清除与签名统计表无关联的签名数据
            DBManager
                    .execSQL("DELETE FROM tb_sign_group WHERE sign_group_key IN(SELECT sg.sign_group_key FROM tb_sign_group sg LEFT JOIN tb_count_sign_info cpi ON cpi.sign_group_key=sg.sign_group_key WHERE cpi.sign_group_key IS NULL)");
        } catch (Throwable e) {
            LogManager.w(Constant.TAG, "CountSignInfoManager deleteHistoryData error", e);
        }
    }

    /**
     * 根据任务队列中的签名密文获取对应的签名明文数据
     * 
     * @param taskInfoList
     * @return
     */
    public static List<String> querySignByTask(List<TaskInfo> taskInfoList) {
        List<String> allCode = getAllCodeByTask(taskInfoList);
        if (allCode == null || allCode.size() == 0) {
            return null;
        }

        XyCursor cursor = null;
        try {
            int size = allCode.size();
            StringBuffer whereClause = new StringBuffer(CODE).append(" IN(")
                    .append(SmsParseRecorder.makePlaceholders(size)).append(")");
            String[] whereArgs = allCode.toArray(new String[size]);
            StringBuilder sbrSql = new StringBuilder();
            sbrSql.append("SELECT DISTINCT ").append(SIGN).append(" FROM ").append(SIGN_GROUP_TABLE_NAME)
                    .append(" WHERE ").append(whereClause);
            cursor = DBManager.rawQuery(sbrSql.toString(), whereArgs);
            if (cursor == null || cursor.getCount() < 1) {
                return null;
            }
            List<String> signList = new ArrayList<String>();
            int signIndex = cursor.getColumnIndex(SIGN);
            while (cursor.moveToNext()) {
                signList.add(cursor.getString(signIndex));
            }
            return signList;
        } catch (Throwable e) {
            LogManager.w(Constant.TAG, "CountSignInfoManager querySignByTask error", e);
        } finally {
            XyCursor.closeCursor(cursor, true);
        }
        return null;
    }

    /**
     * 从任务队列中获取所有签名密文
     * 
     * @param taskInfoList
     * @return
     */
    private static List<String> getAllCodeByTask(List<TaskInfo> taskInfoList) {
        if (taskInfoList == null || taskInfoList.size() < 1) {
            return null;
        }

        List<String> allCode = new ArrayList<String>();
        for (TaskInfo taskInfo : taskInfoList) {
            if (taskInfo == null || taskInfo.content == null) {
                continue;
            }
            allCode.addAll(Arrays.asList(taskInfo.content.split(";")));
        }
        return allCode;
    }

    /**
     * 生成数据组，格式：SHA256(号码_签名1;签名2_识别时间)
     * 
     * @param num
     * @param signGroup
     * @param parseTime
     * @return
     */
    private static String createDataGroup(String num, String signGroup, String parseTime) {
        if (StringUtils.isNull(num) || StringUtils.isNull(signGroup) || StringUtils.isNull(parseTime)) {
            return "";
        }
        return Signaturer.sha256Encode(new StringBuilder().append(num).append(SPLIT2).append(signGroup).append(SPLIT2)
                .append(parseTime).toString());
    }
}
/* RM-1351 huangzhiqiang 20170407 end */
